package com.altocloud.queries;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.altocloud.database.DbUtil;

public class Level2 {
	DbUtil db = new DbUtil();
	Connection conn = db.getDbConnection("226-Level-2");
	Statement stmt = null;
	ResultSet rs = null;
	String sql = "";

	public void avgTemp() {
		try {
			stmt = conn.createStatement();
			long startTime = System.currentTimeMillis();
			sql = "select w.tmpf from weather w, station_details sde where w.stn_id_stn=sde.stn and sde.state='UT' and w.tmpf<>-9999";
			rs = stmt.executeQuery(sql);
			long total = 0;
			long rows = 0;
			while (rs.next()) {
				total = total + rs.getInt(1);
				rows++;
				// System.out.println(rows);
			}
			System.out.println(total);
			System.out.println(rows);
			System.out.println("Avg is: " + total / rows);
			System.out.println("time is:"
					+ (System.currentTimeMillis() - startTime));
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void getHottestState() {
		try {
			stmt = conn.createStatement();
			long startTime = System.currentTimeMillis();
			sql = "select s.state, avg(w.tmpf) from station_details s inner join weather w on w.stn_id_stn=s.stn where w.tmpf<>-9999 and extract (month from w.date)='10' group by s.state order by avg(w.TMPF) desc;";
			rs = stmt.executeQuery(sql);
			String state = "";
			rs.next();
			state = rs.getString(1);
			System.out.println("Hottest Tmep is: " + state);
			System.out.println("time is:"
					+ (System.currentTimeMillis() - startTime));

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void getAvgTempMnet() {
		try {
			stmt = conn.createStatement();
			long startTime = System.currentTimeMillis();
			sql = "select avg(w.tmpf) from weather w, station_details sd where sd.mnet=8 and w.tmpf<>-9999 and sd.stn=w.stn_id_stn";
			rs = stmt.executeQuery(sql);
			int state = 0;
			rs.next();
			state = rs.getInt(1);
			System.out.println("Avg Tmep by mnet is: " + state);
			System.out.println("time is:"
					+ (System.currentTimeMillis() - startTime));

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void getPopularProvider() {
		try {
			stmt = conn.createStatement();
			long startTime = System.currentTimeMillis();
			sql = "select sde.primary_provider_id, count(*) from station_details_extra sde group by sde.primary_provider_id order by count(*) desc";
			rs = stmt.executeQuery(sql);
			String state = "";
			rs.next();
			state = rs.getString(1);
			System.out.println("Popular provider is: " + state);
			System.out.println("time is:"
					+ (System.currentTimeMillis() - startTime));

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void getMaxMinTemp() {
		try {

			stmt = conn.createStatement();
			long startTime = System.currentTimeMillis();
			sql = "select max(w.tmpf), min(w.tmpf), sd.state from weather w, station_details sd where w.stn_id_stn=sd.stn and w.tmpf<>-9999 and to_char(w.date, 'yyyy-mm-dd')='2012-10-01' group by sd.state";
			rs = stmt.executeQuery(sql);
			double max = 0.0;
			double min = 0.0;
			rs.next();
			max = rs.getDouble(1);
			min = rs.getDouble(2);
			System.out.println("max is: " + max);
			System.out.println("min is: " + min);
			System.out.println("time is:"
					+ (System.currentTimeMillis() - startTime));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String args[]) {
		Level2 l1 = new Level2();
		l1.avgTemp();
		l1.getHottestState();
		l1.getAvgTempMnet();
		l1.getPopularProvider();
		l1.getMaxMinTemp();
	}

}
